package com.itheima.shop.dao;

import com.itheima.shop.entity.Category;
import com.itheima.shop.util.JdbcUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class CategoryDao {
    private QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
    /**
     * 查询所有类别数量
     */
    public int getAllRecordNO() throws SQLException {

        String sql = "select count(cid) from category";
        Object[] params = null;
        Long temp = runner.query(sql, new ScalarHandler<>(), params);
        return temp.intValue();
    }
    /**
     * 查询所有类别,分页查询
     */
    public List<Category> findAllCategory(int start, int size) throws SQLException{
        String sql = "select * from  category order by cid asc limit ?,?";
        Object[] params = {start, size};
        return runner.query(sql, new BeanListHandler<>(Category.class), params);
    }

    /**
     * 更新类别
     */
    public int updateCategory(int cid,String cname) throws SQLException {
        String sql = "update category set cname=? where cid=?";
        Object[] params = {cname,cid};
        return runner.update(sql, params);
    }
    /**
     * 删除类别
     */
    public int deleteCategory(int cid) throws SQLException {
        String sql = "delete from category where cid = ?";
        Object[] params = {cid};
        return runner.update(sql, params);
    }
    /**
     * 增加类别
     */
    public int addCategory(String cname) throws SQLException{
        String sql = "insert into category(cname) values(?)";
        Object[] params = {cname};
        return runner.update(sql, params);
    }
    /**
     * 查询所有类别,用于填充form表单
     */
    public List<Category> findAllCategoryAjax() throws SQLException {
        String sql = "select * from  category";
        Object[] params = null;
        return runner.query(sql, new BeanListHandler<>(Category.class), params);
    }
}
